﻿// Ruslán Ledesma Garza (ruslanledesmagarza@gmail.com)
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlServerCe;
using System.Threading;

namespace WindowsFormsApplication1
{
    sealed class EmbeddedDatabase
    {
        /// <summary>
        /// Static instance of the singleton object.
        /// </summary>
        volatile private static EmbeddedDatabase singletonHcledbObject;

        /// <summary>
        /// Static object used as lock for preventing
        /// simultaneous instantiation.
        /// </summary>
        private static object instantiationLock = new Object();

        /// <summary>
        /// Instance connection variable.
        /// </summary>
        private SqlCeConnection hcledbConnection;

        /// <summary>
        /// Instance level object used as lock for preventing
        /// simultaneous operations over the database.
        /// </summary>
        private object dbAccessLock = new Object();

        /// <summary>
        /// Database filename constant.
        /// </summary>
        private const string DATABASEFILE = "data.sdf"; //"data.sdf";

        /// <summary>
        /// The system logger object.
        /// </summary>
        //private Logger logger;

        /// <summary>
        /// Private constructor for singleton pattern.
        /// </summary>
        private EmbeddedDatabase()
        {
            //get the system logger object...
            //logger = Logger.Instance;

            //create connection object...
            hcledbConnection = new SqlCeConnection(@"Data Source = |DataDirectory|\" + DATABASEFILE);
            hcledbConnection.Open();
        }

        /// <summary>
        /// Class destructor
        /// </summary>
        ~EmbeddedDatabase()
        {
            hcledbConnection.Close();
        }

        /// <summary>
        /// Reference to the singleton instance of this class.
        /// </summary>
        public static EmbeddedDatabase Instance
        {
            get
            {
                lock (instantiationLock)
                {
                    if (singletonHcledbObject == null)
                        singletonHcledbObject = new EmbeddedDatabase();
                }

                return singletonHcledbObject;
            }
        }

        /// <summary>
        /// Execute a custom query and return "An updateable, scrollable, and bindable cursor",
        /// i.e. a SqlCeResultSet object.
        /// </summary>
        /// <param name="query">The query string.</param>
        /// <returns>The resulting SqlCeResultSet object.</returns>
        public SqlCeResultSet GetResultSetForCustomQuery(String query)
        {
            lock (dbAccessLock)
            {
                SqlCeCommand cmd = new SqlCeCommand(query, hcledbConnection);
                SqlCeResultSet r = null;
                ///this try prevents "too many sessions" error...
                try
                {
                    r = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
                }
                catch (SqlCeException e)
                {
                    cmd.Dispose();
                    //logger.LogMessage("HackerClientEmbeddedDatabase.GetResultSetForCustomQuery - " + query + " - " + e.Message);
                    return null;
                }
                cmd.Dispose();
                return r;
            }
        }

        /// <summary>
        /// Execute a custom query and return a custom cursor,
        /// i.e. a SqlCeResultSet object.
        /// </summary>
        /// <param name="query">The query string.</param>
        /// <param name="ops">The cursor options.</param>
        /// <returns>The resulting SqlCeResultSet object.</returns>
        public SqlCeResultSet GetResultSetForCustomQuery(String query, ResultSetOptions ops)
        {
            lock (dbAccessLock)
            {
                SqlCeCommand cmd = new SqlCeCommand(query, hcledbConnection);
                SqlCeResultSet r = null;
                ///this try prevents "too many sessions" error...
                try
                {
                    r = cmd.ExecuteResultSet(ops);
                }
                catch (SqlCeException e)
                {
                    cmd.Dispose();
                    //logger.LogMessage("HackerClientEmbeddedDatabase.GetResultSetForCustomQuery - " + query + " - " + e.Message);
                    return null;
                }
                cmd.Dispose();
                return r;
            }
        }

        /// <summary>
        /// Execute a custom query within the specified transaction object and return
        /// "An updateable, scrollable, and bindable cursor",
        /// i.e. a SqlCeResultSet object.
        /// </summary>
        /// <param name="query">The query string.</param>
        /// <param name="transaction">The transaction object.</param>
        /// <returns>The resulting SqlCeResultSet object.</returns>
        public SqlCeResultSet GetResultSetForCustomQuery(String query, SqlCeTransaction transaction)
        {
            lock (dbAccessLock)
            {
                SqlCeCommand cmd = new SqlCeCommand(query, hcledbConnection);
                SqlCeResultSet r = null;
                cmd.Transaction = transaction;
                ///this try prevents "too many sessions" error...
                try
                {
                    r = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
                }
                catch (SqlCeException e)
                {
                    cmd.Dispose();
                    //logger.LogMessage("HackerClientEmbeddedDatabase.GetResultSetForCustomQuery - " + query + " - " + e.Message);
                    return null;
                }
                cmd.Dispose();
                return r;
            }
        }

        /// <summary>
        /// Execute a custom query within the specified transaction object and return
        /// a custom cursor,
        /// i.e. a SqlCeResultSet object.
        /// </summary>
        /// <param name="query">The query string.</param>
        /// <param name="ops">The cursor options.</param>
        /// <param name="transaction">The transction object.</param>
        /// <returns>The resulting SqlCeResultSet object.</returns>
        public SqlCeResultSet GetResultSetForCustomQuery(String query, ResultSetOptions ops, SqlCeTransaction transaction)
        {
            lock (dbAccessLock)
            {
                SqlCeCommand cmd = new SqlCeCommand(query, hcledbConnection);
                SqlCeResultSet r = null;
                cmd.Transaction = transaction;
                ///this try prevents "too many sessions" error...
                try
                {
                    r = cmd.ExecuteResultSet(ops);
                }
                catch (SqlCeException e)
                {
                    cmd.Dispose();
                    //logger.LogMessage("HackerClientEmbeddedDatabase.GetResultSetForCustomQuery - " + query + " - "+ e.Message);
                    return null;
                }
                cmd.Dispose();
                return r;
            }
        }

        /// <summary>
        /// Execute a custom query and return "a forward-only stream of data rows from a data source",
        /// i.e. a SqlCeDataReader object.
        /// </summary>
        /// <param name="query">The query string.</param>
        /// <returns>The resulting SqlCeDataReader object.</returns>
        public SqlCeDataReader GetDataReaderForCustomQuery(String query)
        {
            lock (dbAccessLock)
            {
                SqlCeCommand cmd = new SqlCeCommand(query, hcledbConnection);
                SqlCeDataReader r = null;
                try
                {
                    r = cmd.ExecuteReader();
                }
                catch (SqlCeException e)
                {
                    cmd.Dispose();
                    //logger.LogMessage("HackerClientEmbeddedDatabase.GetDataReaderForCustomQuery - " + query + " - " + e.Message);
                    return null;
                }
                cmd.Dispose();
                return r;
            }
        }

        /// <summary>
        /// Execute a custom query within the specified transaction object and 
        /// return "a forward-only stream of data rows from a data source",
        /// i.e. a SqlCeDataReader object.
        /// </summary>
        /// <param name="query">The query string.</param>
        /// <param name="transaction">The transaction object.</param>
        /// <returns>The resulting SqlCeDataReader object.</returns>
        public SqlCeDataReader GetDataReaderForCustomQuery(String query, SqlCeTransaction transaction)
        {
            lock (dbAccessLock)
            {
                SqlCeCommand cmd = new SqlCeCommand(query, hcledbConnection);
                SqlCeDataReader r = null;
                cmd.Transaction = transaction;
                try
                {
                    r = cmd.ExecuteReader();
                }
                catch (SqlCeException e)
                {
                    cmd.Dispose();
                    //logger.LogMessage("HackerClientEmbeddedDatabase.GetDataReaderForCustomQuery - " + query + " - " + e.Message);
                    return null;
                }
                cmd.Dispose();
                return r;
            }
        }

        /// <summary>
        /// Execute a custom non-query operation over the database.
        /// </summary>
        /// <param name="sqlcommand">The sql statement to be executed.</param>
        /// <returns>The number of rows affected according to SqlCeCommand.ExecuteNonQuery()
        /// method, -2 if there was an exception.</returns>
        public int ExecuteNonQueryOperation(string sqlcommand) 
        {
            lock (dbAccessLock)
            {
                int r = 0;
                SqlCeCommand cmd = new SqlCeCommand(sqlcommand, hcledbConnection);
                try
                {
                    r = cmd.ExecuteNonQuery();
                }
                catch (SqlCeException e)
                {
                    cmd.Dispose();
                    //logger.LogMessage("HackerClientEmbeddedDatabase.ExecuteNonQueryOperation - " + sqlcommand + " - " + e.Message);

                    return -2;
                }
                cmd.Dispose();
                return r;
            }
        }

        /// <summary>
        /// Execute a custom non-query operation over the database within the specified
        /// transaction object.
        /// </summary>
        /// <param name="sqlcommand">The sql statement to be executed.</param>
        /// <param name="transaction">The transction object.</param>
        /// <returns>The number of rows affected according to SqlCeCommand.ExecuteNonQuery()
        /// method, -2 if there was an exception.</returns>
        public int ExecuteNonQueryOperation(string sqlcommand, SqlCeTransaction transaction)
        {
            lock (dbAccessLock)
            {
                int r = 0;
                SqlCeCommand cmd = new SqlCeCommand(sqlcommand, hcledbConnection);
                cmd.Transaction = transaction;
                try
                {
                    r = cmd.ExecuteNonQuery();
                }
                catch (SqlCeException e)
                {
                    cmd.Dispose();
                    //logger.LogMessage("HackerClientEmbeddedDatabase.ExecuteNonQueryOperation - " + sqlcommand + " - " + e.Message);
                    return -2;
                }
                cmd.Dispose();
                return r;
            }
        }

        /// <summary>
        /// Begins a database transaction.
        /// Please see:
        /// SqlCeTransaction.Commit(): http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlcetransaction.commit.aspx
        /// </summary>
        /// <returns>An SqlCeTransaction object representing the transaction.</returns>
        public SqlCeTransaction BeginTransaction()
        {
            lock (dbAccessLock)
            {
                return hcledbConnection.BeginTransaction();
            }
        }

        /// <summary>
        /// Begins a database transaction with the current IsolationLevel value.
        /// Please see:
        /// IsolationLevel: http://msdn2.microsoft.com/en-us/library/system.data.isolationlevel.aspx
        /// CommitModes: http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.commitmode.aspx
        /// </summary>
        /// <param name="il">The IsolationLevel value.</param>
        /// <returns>An SqlCeTransaction object representing the transaction.</returns>
        public SqlCeTransaction BeginTransaction(IsolationLevel il)
        {
            lock (dbAccessLock)
            {
                return hcledbConnection.BeginTransaction(il);
            }
        }
    }
}
